﻿using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using NoSqlTest.Dto;
using NoSqlTest.Model;
using NoSqlTest.ViewModel;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace NoSqlTest.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class FreeSqlController : ControllerBase
    {
        private readonly IFreeSql _freeSql;
        private readonly FreeSqlContext _freeSqlContext;
        private readonly ILogger<FreeSqlController> _logger;
        public FreeSqlController(FreeSqlContext freeSqlContext, IFreeSql freeSql, ILogger<FreeSqlController> logger)
        {
            this._freeSql = freeSql;
            this._freeSqlContext = freeSqlContext;
            this._logger = logger;
        }

        /// <summary>
        /// 添加班级
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        [HttpPost("AddClass/{name}")]
        public IActionResult AddClass(string name)
        {
            ClassGrade classGrade = new ClassGrade { Name = name };
            var classid = _freeSql.Insert(classGrade).ExecuteIdentity();
            return Ok(classid);
        }
        /// <summary>
        /// 添加课程和老师
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        [HttpPost("AddCourse/{name}")]
        public IActionResult AddCourse(string name)
        {
            Course classGrade = new Course { Name = name, Teacher = name + "老师" };
            var classid = _freeSql.Insert(classGrade).ExecuteIdentity();
            return Ok(classid);
        }
        /// <summary>
        /// 给班级添加课程
        /// </summary>
        /// <param name="classAddCourse"></param>
        /// <returns></returns>
        [HttpPost("ClassAddCourse")]
        public IActionResult ClassAddCourse([FromBody] ClassAddCourse classAddCourse)
        {
            var date = _freeSql.Select<ClassGrade>()
                .IncludeMany(t => t.Classs)
                .Where(t => t.Id == classAddCourse.ClassId).First();
            var Classs = new List<MiddleClassCourse>();
            foreach (var i in classAddCourse.CourseIds)
            {
                if (!date.Classs.Select(t => t.Id).Contains(i))
                {
                    Classs.Add(new MiddleClassCourse
                    {
                        ClassId = date.Id,
                        CourseId = i
                    });
                }
            }
            return Ok(_freeSql.Insert(Classs).ExecuteAffrows());
        }
        /// <summary>
        /// 插入学生
        /// </summary>
        /// <param name="student"></param>
        /// <returns></returns>
        [HttpPost("AddStudent")]
        public IActionResult AddStudent([FromBody] AddStudent student)
        {
            try
            {
                StringBuilder str = new StringBuilder();
                Stopwatch stopwatch2 = new Stopwatch();
                stopwatch2.Start();
                List<Student> Students11 = new List<Student>();
                for (int i = 0; i < student.Count; i++)
                {
                    Students11.Add(new Student
                    {
                        ClassId = student.ClassId,
                        Name = student.Count.ToString(),
                        Age = 20,
                        Sex = 1,
                    });
                }
                _freeSqlContext.Students.AddRange(Students11);
                _freeSqlContext.SaveChanges();
                stopwatch2.Stop();
                str.Append($"DbContext插入{student.Count}条数据耗时：" + stopwatch2.ElapsedMilliseconds.ToString());

                //Stopwatch stopwatch3 = new Stopwatch();
                //stopwatch3.Start();
                //List<Student> Students = new List<Student>();
                //for (int i = 0; i < student.count; i++)
                //{
                //    Students.Add(
                //        new Student
                //        {
                //            ClassId = student.classId,
                //            Name = student.count.ToString(),
                //            Age = 20,
                //            Sex = 1,
                //        });
                //}
                //_freeSql.Insert<Student>(Students).ExecuteAffrows();
                //stopwatch3.Stop();
                //str.AppendLine($"批量插入{student.count}条数据耗时：" + stopwatch3.ElapsedMilliseconds.ToString());

                _logger.LogInformation(str.ToString());
                return Ok(str.ToString());
            }
            catch (Exception ex)
            {
                return Ok(ex.Message);
            }
        }
        /// <summary>
        /// 插入学生-带选修课程
        /// </summary>
        /// <param name="course"></param>
        /// <returns></returns>
        [HttpPost("AddStudentCourse")]
        public IActionResult AddStudentCourse([FromBody] AddStudentCourse course)
        {
            try
            {
                StringBuilder str = new StringBuilder();
                #region 自增主键限制，不能使用
                //Stopwatch stopwatch1 = new Stopwatch();
                //stopwatch1.Start();
                //for (int i = 0; i < course.StudentCount; i++)
                //{
                //    _freeSqlContext.Students.Add(new Student
                //    {
                //        ClassId = course.ClassId,
                //        Name = i.ToString(),
                //        Age = 22,
                //        Sex = 0,
                //        Courses =new List<MiddleStudentCourse> { new MiddleStudentCourse { CourseId = course.CourseId } }
                //    });
                //}
                //_freeSqlContext.SaveChanges();
                //stopwatch1.Stop();
                //str.AppendLine($"方法1插入{course.StudentCount * 2}条数据耗时：" + stopwatch1.ElapsedMilliseconds.ToString());
                #endregion

                Stopwatch stopwatch2 = new Stopwatch();
                stopwatch2.Start();
                for (int i = 0; i < course.StudentCount; i++)
                {
                    var id = _freeSql.Insert<Student>(new Student
                    {
                        ClassId = course.ClassId,
                        Name = i.ToString(),
                        Age = 22,
                        Sex = 0,
                    }).ExecuteIdentity();

                    MiddleStudentCourse msc = new MiddleStudentCourse { StudentId = (int)id, CourseId = course.CourseId };
                    _freeSql.Insert<MiddleStudentCourse>(msc).ExecuteAffrows();
                }
                stopwatch2.Stop();
                str.Append($"方法2插入{course.StudentCount * 2}条数据耗时：" + stopwatch2.ElapsedMilliseconds.ToString());

                _logger.LogInformation(str.ToString());
                return Ok(str.ToString());
            }
            catch (Exception ex)
            {
                return Ok(ex.Message);
            }
        }
        /// <summary>
        /// 查询学生
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpGet("GetStudent/{id}")]
        public IActionResult GetStudent(int id)
        {
            try
            {
                StringBuilder str = new StringBuilder();
                Stopwatch stopwatch1 = new Stopwatch();
                stopwatch1.Start();
                var student1 = _freeSql.Select<Student>()
                    .Include(a => a.Class)
                    .IncludeMany(a => a.Courses, then => then.Include(t => t.Course))
                    .Where(a => a.Id == id)
                    .First(a => new StudentDto
                    {
                        ClassName = a.Class.Name,
                        //CourseIds = a.Courses.AsSelect().ToList(a => a.CourseId)//一对多失败
                        //Courses = a.Courses.AsSelect().ToList(a => a.Course),//一对多失败
                        //Courses = a.Courses.AsSelect().ToList(t => new Course { Id = t.Course.Id, Name = t.Course.Name })//一对多失败
                    });

                stopwatch1.Stop();
                str.Append($"IFreeSql查询数据耗时：" + stopwatch1.ElapsedMilliseconds.ToString());

                {
                    //Stopwatch stopwatch2 = new Stopwatch();
                    //stopwatch2.Start();
                    //var student = _freeSqlContext.Students.Select
                    //    .Include(a => a.Class)
                    //    .IncludeMany(a => a.Courses, then => then.Include(t => t.Course))
                    //    .Where(a => a.Id == id + 1)
                    //    .First(a => new StudentDto
                    //    {
                    //        ClassName = a.Class.Name,
                    //    });
                    //stopwatch2.Stop();
                    //str.AppendLine($"DbContext查询数据耗时：" + stopwatch2.ElapsedMilliseconds.ToString());
                }

                _logger.LogInformation(str.ToString());
                return Ok(student1);
            }
            catch (Exception ex)
            {
                return Ok(ex.Message);
            }
        }
        /// <summary>
        /// 查询班级中的所有学生
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpGet("GetClassStudent/{id}")]
        public IActionResult GetClassStudent(int id)
        {
            try
            {
                StringBuilder str = new StringBuilder();
                Stopwatch stopwatch2 = new Stopwatch();
                stopwatch2.Start();
                var students = _freeSqlContext.ClassGrades.Select
                    .IncludeMany(a => a.Students)
                    .Where(a => a.Id == id)
                    .First();
                stopwatch2.Stop();
                str.Append($"DbContext查询数据耗时：" + stopwatch2.ElapsedMilliseconds.ToString());

                {
                    //Stopwatch stopwatch1 = new Stopwatch();
                    //stopwatch1.Start();
                    //var students1 = _freeSql.Select<ClassGrade>()
                    //    .IncludeMany(a => a.Students)
                    //    .Where(a => a.Id == id)
                    //    .ToSql();
                    //stopwatch1.Stop();
                    //str.AppendLine($"IFreeSql查询数据耗时：" + stopwatch1.ElapsedMilliseconds.ToString());
                }

                _logger.LogInformation(str.ToString());
                return Ok(str.ToString());
            }
            catch (Exception ex)
            {
                return Ok(ex.Message);
            }
        }
        /// <summary>
        /// 查询学生的所有课程
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpGet("GetStudentCourse/{id}")]
        public IActionResult GetStudentCourse(int id)
        {
            try
            {
                StringBuilder str = new StringBuilder();
                Stopwatch stopwatch1 = new Stopwatch();
                stopwatch1.Start();
                var student = _freeSql.Select<Student>().Where(t => t.Id == id).First();
                var item1 = _freeSql.Select<MiddleClassCourse>().From<Course>((a, b) => a
                    .LeftJoin(a => a.CourseId == b.Id)
                    .Where(a => a.ClassId == student.ClassId))
                    .ToList((a, b) => new { b.Id, b.Name });

                var item2 = _freeSql.Select<MiddleStudentCourse>().From<Course>((a, b) => a
                    .LeftJoin(a => a.CourseId == b.Id)
                    .Where(a => a.StudentId == id))
                    .ToList((a, b) => new { b.Id, b.Name });

                item1.AddRange(item2);

                stopwatch1.Stop();
                str.Append($"查询数据耗时：" + stopwatch1.ElapsedMilliseconds.ToString());

                _logger.LogInformation(str.ToString());
                return Ok(str.ToString());
            }
            catch (Exception ex)
            {
                return Ok(ex.Message);
            }
        }
        /// <summary>
        /// 修改学生名
        /// </summary>
        /// <param name="student"></param>
        /// <returns></returns>
        [HttpPost("UpdateStudent")]
        public IActionResult UpdateStudent([FromBody] UpdateStudent student)
        {
            try
            {
                StringBuilder str = new StringBuilder();
                Stopwatch stopwatch3 = new Stopwatch();
                stopwatch3.Start();
                _freeSqlContext.Students
                    .Where(a => a.Id == student.Id)
                    .ToUpdate()
                    .Set(a => a.Name, student.Name)
                    .Set(a => a.Age, student.Age)
                    .Set(a => a.Sex, student.Sex)
                    .ExecuteAffrows();
                _freeSqlContext.SaveChanges();
                stopwatch3.Stop();
                str.Append($"DbContext修改数据耗时：" + stopwatch3.ElapsedMilliseconds.ToString());

                {
                    //Stopwatch stopwatch1 = new Stopwatch();
                    //stopwatch1.Start();
                    //_freeSql.Update<Student>()
                    //    .Set(a => a.Name, student.Name)
                    //    .Set(a => a.Sex, student.Sex)
                    //    .Set(a => a.Age, student.Age)
                    //    .Where(a => a.Id == student.Id)
                    //    .ExecuteAffrows();
                    //stopwatch1.Stop();
                    //str.AppendLine($"IFreeSql 方法1 修改数据耗时：" + stopwatch1.ElapsedMilliseconds.ToString());

                    //Stopwatch stopwatch4 = new Stopwatch();
                    //stopwatch4.Start();
                    //var repo = _freeSql.GetRepository<Student>();
                    //var item = new Student { Id = student.Id };
                    //repo.Attach(item); //此时快照 item
                    //item.Name = student.Name;
                    //item.Sex = student.Sex;
                    //item.Age = student.Age;
                    //repo.Update(item); //对比快照时的变化
                    //stopwatch4.Stop();
                    //str.AppendLine($"Repository修改数据耗时：" + stopwatch4.ElapsedMilliseconds.ToString());
                }

                _logger.LogInformation(str.ToString());
                return Ok(str.ToString());
            }
            catch (Exception ex)
            {
                return Ok(ex.Message);
            }
        }
    }
}
